/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao.backup;

import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * 备份信息Dao实现
 * @author MaShibo
 * @date 2017/12/27.
 */
@Repository
public class BackupInfosDao {

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	public int queryObuIdsNum(){
		final String sql =
				"SELECT COUNT(1)\n" +
						"  FROM (SELECT A.*,\n" +
						"               ROW_NUMBER() OVER(PARTITION BY A.OBULISTID ORDER BY A.OPTON DESC) AS RM\n" +
						"          FROM AC_OBU A, AC_BANKCARD B, AC_CARDINFO C\n" +
						"         WHERE A.BANKCARDID = B.BANKCARDID\n" +
						"           AND C.ECARDNO = B.ECARDNO\n" +
						"           AND A.CARDINFOID IS NULL\n" +
						"           AND A.STATE = 1)\n" +
						" WHERE RM = 1";
		return dzzfJdbcTemplate.queryForObject(sql, Integer.class);
	}

	@SuppressWarnings("unchecked")
	public List<OBU> queryObuInfosByRow(int startRow, int endRow){
		final String sql =
				"SELECT *\n" +
						"  FROM (SELECT M.*, ROWNUM AS RN\n" +
						"          FROM (SELECT A.*,\n" +
						"                       ROW_NUMBER() OVER(PARTITION BY A.OBULISTID ORDER BY A.OPTON DESC) AS RM\n" +
						"                  FROM AC_OBU A, AC_BANKCARD B, AC_CARDINFO C\n" +
						"                 WHERE A.BANKCARDID = B.BANKCARDID\n" +
						"                   AND C.ECARDNO = B.ECARDNO\n" +
						"                   AND A.CARDINFOID IS NULL\n" +
						"                   AND A.STATE = 1) M\n" +
						"         WHERE RM = 1)\n" +
						" WHERE RN >= ?\n" +
						"   AND RN <= ?";
		return (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), startRow, endRow);
	}
}
